/*
 * @(#)FormRecord.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.form.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import com.dcivision.form.bean.FormElementType;
import com.dcivision.form.bean.FormRecord;
import com.dcivision.form.bean.FormSection;
import com.dcivision.form.bean.FormSubmission;
import com.dcivision.form.web.ListFormRecordForm;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.workflow.bean.WorkflowRecord;
import com.dcivision.workflow.bean.WorkflowStep;
import com.dcivision.workflow.dao.MtmWfStepFormRecordDAObject;
import com.dcivision.workflow.dao.WorkflowRecordDAObject;

/**
  FormRecordDAObject.java

  This class is the data access bean for table "FORM_RECORD".

  @author      Scott Tong
  @company     DCIVision Limited
  @creation date   15/12/2003
  @version     $Revision: 1.64.2.1 $
*/

public class FormRecordDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.64.2.1 $";

  public static final String TABLE_NAME = "FORM_RECORD";  

  public FormRecordDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }

  
  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("FORM_CODE");
    this.vecDBColumn.add("FORM_VERSION");
    this.vecDBColumn.add("FORM_TYPE");
    this.vecDBColumn.add("FORM_DOC_ID");
    this.vecDBColumn.add("FORM_GROUP_ID");
    this.vecDBColumn.add("TITLE");
    this.vecDBColumn.add("TITLE_ALT");
    this.vecDBColumn.add("DESCRIPTION");
    this.vecDBColumn.add("DESCRIPTION_ALT");
    this.vecDBColumn.add("STATUS");
    this.vecDBColumn.add("START_DATE");
    this.vecDBColumn.add("END_DATE");
    this.vecDBColumn.add("DISPLAY_ELEMENT_ID");
    this.vecDBColumn.add("FORM_CATEGORY_ID");
    this.vecDBColumn.add("TRACK_CLASS");
    this.vecDBColumn.add("SUBMISSION_CLASS");
    this.vecDBColumn.add("AUTO_NUMBERING");
    this.vecDBColumn.add("LAST_PAGE_CODE");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
//    this.vecDBColumn.add("PREFIX");
//    this.vecDBColumn.add("SUFFIX");
//    this.vecDBColumn.add("START_NO");
//    this.vecDBColumn.add("LENGTH");
//    this.vecDBColumn.add("CURRENT_NO");
//    this.vecDBColumn.add("INREMENT");   
    
  }

  
  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.FORM_CATEGORY_NAME ");
//        sqlStat.append("FROM   FORM_RECORD A, SETUP_OPTION_FORM_CATEGORY B ");
//        sqlStat.append("WHERE  A.FORM_CATEGORY_ID = B.ID AND A.ID = ? AND A.RECORD_STATUS = ? ");
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
      	sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
//          tmpFormRecord.setFormCategory(getResultSetString(rs, "FORM_CATEGORY_NAME"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          return(tmpFormRecord);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());


    synchronized(dbConn) {
      try {

//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.FORM_CATEGORY_NAME ");
//        sqlStat.append("FROM   FORM_RECORD A, SETUP_OPTION_FORM_CATEGORY B ");
//        sqlStat.append("WHERE  A.FORM_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");      	
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt<pageSize) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
//          tmpFormRecord.setFormCategory(getResultSetString(rs, "FORM_CATEGORY_NAME"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));          
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          tmpFormRecord.setRecordCount(totalNumOfRecord);
          tmpFormRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }

  
  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.FORM_CATEGORY_NAME ");
//        sqlStat.append("FROM   FORM_RECORD A, SETUP_OPTION_FORM_CATEGORY B ");
//        sqlStat.append("WHERE  A.FORM_CATEGORY_ID = B.ID AND A.RECORD_STATUS = ? ");
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");        
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
//          tmpFormRecord.setFormCategory(getResultSetString(rs, "FORM_CATEGORY_NAME"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    ResultSet sqlRsult = null;
    FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();
    synchronized(dbConn) {
      try {
        sqlStat=new StringBuffer("");
        sqlStat.append("SELECT COUNT(*) ");
        sqlStat.append("FROM FORM_RECORD  ");
        sqlStat.append("WHERE FORM_CODE= ? AND FORM_VERSION=? AND RECORD_STATUS = ? ");

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpFormRecord.getFormCode());
        setPrepareStatement(preStat, 2, tmpFormRecord.getFormVersion());
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);

        sqlRsult = preStat.executeQuery();

        if (sqlRsult.next() && sqlRsult.getInt(1)>0 ) {
          throw new ApplicationException(ErrorConstant.SYS_DUPLICATE_INDEX_FORM);
        }
      } catch (java.sql.SQLException sqle) {
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } finally {
        try { sqlRsult.close(); } catch (Exception ignore) {} finally { sqlRsult = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    ResultSet sqlRsult = null;
    FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();
    synchronized(dbConn) {
      try {
        sqlStat=new StringBuffer("");
        sqlStat.append("SELECT COUNT(*)  ");
        sqlStat.append("FROM FORM_RECORD  ");
        sqlStat.append("WHERE FORM_CODE= ? AND FORM_VERSION=? AND RECORD_STATUS = ? AND ID <> ? ");

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpFormRecord.getFormCode());
        setPrepareStatement(preStat, 2, tmpFormRecord.getFormVersion());
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 4, tmpFormRecord.getID());

        sqlRsult = preStat.executeQuery();

        if (sqlRsult.next() && sqlRsult.getInt(1)>0) {
          throw new ApplicationException(ErrorConstant.SYS_DUPLICATE_INDEX_FORM);
        }
      } catch (java.sql.SQLException sqle) {
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } finally {
        try { sqlRsult.close(); } catch (Exception ignore) {} finally { sqlRsult = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }

  
  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();
    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        java.sql.ResultSet sqlRsult;
       if (isExitRecord(tmpFormRecord,"insert")) {
          sqlStat=new StringBuffer("");
          sqlStat.append("INSERT ");
//          sqlStat.append("INTO   FORM_RECORD(ID, FORM_CODE, FORM_VERSION, FORM_TYPE, FORM_GROUP_ID, TITLE, TITLE_ALT, DESCRIPTION, DESCRIPTION_ALT, STATUS, START_DATE, END_DATE, FORM_CATEGORY_ID, TRACK_CLASS, SUBMISSION_CLASS, AUTO_NUMBERING, LAST_PAGE_CODE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, FORM_DOC_ID, PREFIX, SUFFIX ,START_NO ,LENGTH ,CURRENT_NO ,INCREMENT) ");
//          sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
          sqlStat.append("INTO   FORM_RECORD(ID, FORM_CODE, FORM_VERSION, FORM_TYPE, FORM_GROUP_ID, TITLE, TITLE_ALT, DESCRIPTION, DESCRIPTION_ALT, STATUS, START_DATE, END_DATE, DISPLAY_ELEMENT_ID, FORM_CATEGORY_ID, TRACK_CLASS, SUBMISSION_CLASS, AUTO_NUMBERING, LAST_PAGE_CODE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE, FORM_DOC_ID) ");
          sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");          
          preStat = dbConn.prepareStatement(sqlStat.toString());
          setPrepareStatement(preStat, 1, nextID);
          setPrepareStatement(preStat, 2, tmpFormRecord.getFormCode());
          setPrepareStatement(preStat, 3, tmpFormRecord.getFormVersion());
          setPrepareStatement(preStat, 4, tmpFormRecord.getFormType());
          setPrepareStatement(preStat, 5, tmpFormRecord.getFormGroupID());
          setPrepareStatement(preStat, 6, tmpFormRecord.getTitle());
          setPrepareStatement(preStat, 7, tmpFormRecord.getTitleAlt());
          setPrepareStatement(preStat, 8, tmpFormRecord.getDescription());
          setPrepareStatement(preStat, 9, tmpFormRecord.getDescriptionAlt());
          setPrepareStatement(preStat, 10, tmpFormRecord.getStatus());
          setPrepareStatement(preStat, 11, tmpFormRecord.getStartDate());
          setPrepareStatement(preStat, 12, tmpFormRecord.getEndDate());
          setPrepareStatement(preStat, 13, tmpFormRecord.getDisplayElementID());
          setPrepareStatement(preStat, 14, tmpFormRecord.getFormCategoryID());
          setPrepareStatement(preStat, 15, tmpFormRecord.getTrackClass());
          setPrepareStatement(preStat, 16, tmpFormRecord.getSubmissionClass());
          setPrepareStatement(preStat, 17, tmpFormRecord.getAutoNumbering());
          setPrepareStatement(preStat, 18, tmpFormRecord.getLastPageCode());
          setPrepareStatement(preStat, 19, GlobalConstant.RECORD_STATUS_ACTIVE);
          setPrepareStatement(preStat, 20, new Integer(0));
          setPrepareStatement(preStat, 21, sessionContainer.getUserRecordID());
          setPrepareStatement(preStat, 22, currTime);
          setPrepareStatement(preStat, 23, sessionContainer.getUserRecordID());
          setPrepareStatement(preStat, 24, currTime);
          setPrepareStatement(preStat, 25, tmpFormRecord.getFormDocID());
//          setPrepareStatement(preStat, 25, tmpFormRecord.getPrefix());
//          setPrepareStatement(preStat, 26, tmpFormRecord.getSuffix());
//          setPrepareStatement(preStat, 27, tmpFormRecord.getStart_no());
//          setPrepareStatement(preStat, 28, tmpFormRecord.getLength());
//          setPrepareStatement(preStat, 29, tmpFormRecord.getStart_no());
//          setPrepareStatement(preStat, 30, tmpFormRecord.getIncrement());
          preStat.executeUpdate();
          tmpFormRecord.setID(nextID);
          tmpFormRecord.setCreatorID(sessionContainer.getUserRecordID());
          tmpFormRecord.setCreateDate(currTime);
          tmpFormRecord.setUpdaterID(sessionContainer.getUserRecordID());
          tmpFormRecord.setUpdateDate(currTime);
          tmpFormRecord.setUpdateCount(new Integer(0));
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(
              tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(
              tmpFormRecord.getUpdaterID()));
          return (tmpFormRecord);
        } else {
        return null;
      }

      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();

    synchronized(dbConn) {

    try {
        int updateCnt = 0;
         Timestamp currTime = Utility.getCurrentTimestamp();
         java.sql.ResultSet sqlRsult;

       if (isExitRecord(tmpFormRecord,"update")){
          sqlStat=new StringBuffer("");
          sqlStat.append("UPDATE FORM_RECORD ");
//          sqlStat.append("SET  FORM_CODE=?, FORM_VERSION=?, FORM_TYPE=?, FORM_GROUP_ID=?, TITLE=?, TITLE_ALT=?, DESCRIPTION=?, DESCRIPTION_ALT=?, STATUS=?, START_DATE=?, END_DATE=?, FORM_CATEGORY_ID=?, TRACK_CLASS=?, SUBMISSION_CLASS=?, AUTO_NUMBERING=?, LAST_PAGE_CODE=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=?, FORM_DOC_ID=?, PREFIX=?, SUFFIX=?, START_NO=?, LENGTH=?,CURRENT_NO=?, INCREMENT=? ");
          sqlStat.append("SET  FORM_CODE=?, FORM_VERSION=?, FORM_TYPE=?, FORM_GROUP_ID=?, TITLE=?, TITLE_ALT=?, DESCRIPTION=?, DESCRIPTION_ALT=?, STATUS=?, START_DATE=?, END_DATE=?, DISPLAY_ELEMENT_ID=?, FORM_CATEGORY_ID=?, TRACK_CLASS=?, SUBMISSION_CLASS=?, AUTO_NUMBERING=?, LAST_PAGE_CODE=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=?, FORM_DOC_ID=? ");
          sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");          
          preStat = dbConn.prepareStatement(sqlStat.toString());
          setPrepareStatement(preStat, 1, tmpFormRecord.getFormCode());
          setPrepareStatement(preStat, 2, tmpFormRecord.getFormVersion());
          setPrepareStatement(preStat, 3, tmpFormRecord.getFormType());
          setPrepareStatement(preStat, 4, tmpFormRecord.getFormGroupID());
          setPrepareStatement(preStat, 5, tmpFormRecord.getTitle());
          setPrepareStatement(preStat, 6, tmpFormRecord.getTitleAlt());
          setPrepareStatement(preStat, 7, tmpFormRecord.getDescription());
          setPrepareStatement(preStat, 8, tmpFormRecord.getDescriptionAlt());
          setPrepareStatement(preStat, 9, tmpFormRecord.getStatus());
          setPrepareStatement(preStat, 10, tmpFormRecord.getStartDate());
          setPrepareStatement(preStat, 11, tmpFormRecord.getEndDate());
          setPrepareStatement(preStat, 12, tmpFormRecord.getDisplayElementID());
          setPrepareStatement(preStat, 13, tmpFormRecord.getFormCategoryID());
          setPrepareStatement(preStat, 14, tmpFormRecord.getTrackClass());
          setPrepareStatement(preStat, 15, tmpFormRecord.getSubmissionClass());
          setPrepareStatement(preStat, 16, tmpFormRecord.getAutoNumbering());
          setPrepareStatement(preStat, 17, tmpFormRecord.getLastPageCode());
          setPrepareStatement(preStat, 18, new Integer(tmpFormRecord.getUpdateCount().intValue() + 1));
          setPrepareStatement(preStat, 19, sessionContainer.getUserRecordID());
          setPrepareStatement(preStat, 20, currTime);
          setPrepareStatement(preStat, 21, tmpFormRecord.getFormDocID());
//          setPrepareStatement(preStat, 21, tmpFormRecord.getPrefix());
//          setPrepareStatement(preStat, 22, tmpFormRecord.getSuffix());
//          setPrepareStatement(preStat, 23, tmpFormRecord.getStart_no());
//          setPrepareStatement(preStat, 24, tmpFormRecord.getLength());
//          setPrepareStatement(preStat, 25, tmpFormRecord.getCurrent_no());
//          setPrepareStatement(preStat, 26, tmpFormRecord.getIncrement());          
          setPrepareStatement(preStat, 22, tmpFormRecord.getID());
          setPrepareStatement(preStat, 23, tmpFormRecord.getUpdateCount());

          updateCnt = preStat.executeUpdate();
          if (updateCnt == 0) {
            throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
          }
          else {
            tmpFormRecord.setUpdaterID(sessionContainer.getUserRecordID());
            tmpFormRecord.setUpdateDate(currTime);
            tmpFormRecord.setUpdateCount(new Integer(tmpFormRecord.getUpdateCount().intValue() + 1));
            tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
            tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
            return (tmpFormRecord);
          }
       } else {
        return null;
      }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized boolean isExitRecord(AbstractBaseObject obj, String operType) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();
    synchronized(dbConn) {
      try {
        
        Timestamp currTime = Utility.getCurrentTimestamp();
        java.sql.ResultSet sqlRsult;
        sqlStat=new StringBuffer("");
        sqlStat.append("SELECT * ");
        sqlStat.append("FROM FORM_RECORD  ");
        sqlStat.append("WHERE FORM_CODE= ? AND FORM_VERSION=? AND RECORD_STATUS = ?");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpFormRecord.getFormCode());
        setPrepareStatement(preStat, 2, tmpFormRecord.getFormVersion());
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        sqlRsult = preStat.executeQuery();
      if (operType.equals("insert")) {
      if (sqlRsult.next()) {
        throw new ApplicationException(ErrorConstant.SYS_DUPLICATE_INDEX_FORM);
      } else {
        return  true;
      }
          }else{
      if (sqlRsult.next()){
         if (sqlRsult.getInt("ID")!=tmpFormRecord.getID().intValue()) {
          throw new ApplicationException(ErrorConstant.SYS_DUPLICATE_INDEX_FORM);
        } else {
          return true;
        }
          } else {
        return true;
      }
      }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }


  
  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   FORM_RECORD ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, tmpFormRecord.getID());
        setPrepareStatement(preStat, 2, tmpFormRecord.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpFormRecord);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  
  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    FormRecord tmpFormRecord = (FormRecord)this.oldValue;

    if (tmpFormRecord != null) {
      oldValues.add(toAuditTrailValue(tmpFormRecord.getID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getFormCode()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getFormVersion()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getFormType()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getFormDocID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getFormGroupID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getTitle()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getTitleAlt()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getDescription()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getDescriptionAlt()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getStatus()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getStartDate()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getEndDate()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getDisplayElementID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getFormCategoryID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getTrackClass()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getSubmissionClass()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getAutoNumbering()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getLastPageCode()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpFormRecord.getUpdateDate()));
//      oldValues.add(toAuditTrailValue(tmpFormRecord.getPrefix()));
//      oldValues.add(toAuditTrailValue(tmpFormRecord.getSuffix()));
//      oldValues.add(toAuditTrailValue(tmpFormRecord.getStart_no()));
//      oldValues.add(toAuditTrailValue(tmpFormRecord.getLength()));
//      oldValues.add(toAuditTrailValue(tmpFormRecord.getCurrent_no()));
//      oldValues.add(toAuditTrailValue(tmpFormRecord.getIncrement()));
    }

    tmpFormRecord = (FormRecord)obj;
    if (tmpFormRecord != null) {
      newValues.add(toAuditTrailValue(tmpFormRecord.getID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getFormCode()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getFormVersion()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getFormType()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getFormDocID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getTitle()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getFormGroupID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getTitleAlt()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getDescription()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getDescriptionAlt()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getStatus()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getStartDate()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getEndDate()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getDisplayElementID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getFormCategoryID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getTrackClass()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getSubmissionClass()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getAutoNumbering()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getLastPageCode()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpFormRecord.getUpdateDate()));
//      newValues.add(toAuditTrailValue(tmpFormRecord.getPrefix()));
//      newValues.add(toAuditTrailValue(tmpFormRecord.getSuffix()));
//      newValues.add(toAuditTrailValue(tmpFormRecord.getStart_no()));
//      newValues.add(toAuditTrailValue(tmpFormRecord.getLength()));
//      newValues.add(toAuditTrailValue(tmpFormRecord.getCurrent_no()));
//      newValues.add(toAuditTrailValue(tmpFormRecord.getIncrement()));
      
    }

    auditTrailBase(opMode, oldValues, newValues);
  }

  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/
  public synchronized List getListCanSubmit(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    ListFormRecordForm formRecordForm = (ListFormRecordForm)searchForm;

    WorkflowRecordDAObject wfRecordDAO = new WorkflowRecordDAObject(this.sessionContainer, this.dbConn);
    synchronized(dbConn) {
      try {     
        
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.GROUP_NAME, A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.GROUP_NAME ");
        sqlStat.append("FROM   FORM_RECORD A, FORM_GROUP B ");
        sqlStat.append("WHERE  A.STATUS = ? AND A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND A.FORM_GROUP_ID = B.ID ");
        sqlStat.append("AND    ? >= A.START_DATE  ");
        sqlStat.append("AND    (? <= A.END_DATE OR A.END_DATE IS NULL)  ");
        
        //Check Permission.
        List listID = this.sessionContainer.getPermissionManager().getAccessibleObjectIDList(GlobalConstant.OBJECT_TYPE_FORM, "F");
        if (Utility.isEmpty(listID)) {
          sqlStat.append(" AND B.ID < 0 ");  // Hide all folders.
        } else {
          sqlStat.append(" AND (B.ID = " + ((Integer)listID.get(0)).toString());
          for (int i = 1 ; i < listID.size(); i++) {
            sqlStat.append(" OR B.ID = " + ((Integer)listID.get(i)).toString());
          }
          sqlStat.append(") ");
        }

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = searchForm.getBasicSearchField();
          if (searchField.indexOf(".") < 0) {
            if ("GROUP_NAME".equals(searchField)){
              searchField = getSearchColumn(searchField,"B");
            }else{
              searchField = getSearchColumn(searchField,"A");
            }
		      }
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");          
        }
        if (!Utility.isEmpty(formRecordForm.getSearchFormType())){
          sqlStat.append("AND A.FORM_TYPE = '" + formRecordForm.getSearchFormType() + "' ");
        }
        
        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            if ("GROUP_NAME".equals(sortAttribute)){
              sortAttribute = "B." + sortAttribute;
            }else{
              sortAttribute = "A." + sortAttribute;
            }
          }
          

          //format the sql for any 'LIKE' statement contained
          sqlStat = this.getFormattedSQL(sqlStat.toString());

          
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }
 
        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        Timestamp currTime = Utility.getCurrentTimestamp();
        Timestamp tmrTime = new Timestamp(currTime.getTime());

        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());

        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_RELEASED); 
        this.setPrepareStatement(preStatCnt, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, 4, currTime);
        this.setPrepareStatement(preStatCnt, 5, tmrTime);

        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
         // this.setPrepareStatement(preStatCnt, 4, searchKeyword);
          this.setPrepareStatement(preStatCnt, 6, searchKeyword);
        }

        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }

        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_RELEASED);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 4, currTime);
        this.setPrepareStatement(preStat, 5, tmrTime);
        
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
         // this.setPrepareStatement(preStat, 4, searchKeyword);
          this.setPrepareStatement(preStat, 6, searchKeyword);
        }

        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next()) {// && rowLoopCnt<pageSize //EDIT BY TC ,THERE Will be listing all forms which user have the permission to And ignore the permission of workflow which the form bind to 
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setGroupName(getResultSetString(rs, "GROUP_NAME"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
//          tmpFormRecord.setFormCategory(getResultSetString(rs, "FORM_CATEGORY_NAME"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));
          
          
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          tmpFormRecord.setRecordCount(totalNumOfRecord);
          
          //get the bind workflow list
          List workflowList = wfRecordDAO.getBindListByFormRecordIDFormGroupID(tmpFormRecord.getID(), tmpFormRecord.getFormGroupID());
          if (!Utility.isEmpty(workflowList)) {
            tmpFormRecord.setWorkflowList(workflowList);
            tmpFormRecord.setWorkflowListSize(workflowList.size());
          }

          /*
          List accessWorkflowIDsList = wfRecordDAO.getAccessWorkFlowIDs();
          if (isValidFormSubmission(accessWorkflowIDsList, tmpFormRecord)) {           
            List accessListOfForm=getAccessWorkflowOfForm(accessWorkflowIDsList,workflowList);
            tmpFormRecord.setWorkflowList(accessListOfForm);
            tmpFormRecord.setWorkflowListSize(accessListOfForm.size() );
          }*///EDIT BY TC,THERE IS NO NEED TO CONSIDE ABOUT THE WORKFLOW'S PERMISSION
          //BASE THE Request that only have the form permission,user can submit the form and start the workflow which bind to
          //And only have the workflow permission can submit the form too.
          /*
           * if the both permission of workflow and form,Use this way:
           *   while (rs.next()&& rowLoopCnt<pageSize) { 
           *   ....
           *   List accessWorkflowIDsList = wfRecordDAO.getAccessWorkFlowIDs();
           *   if (isValidFormSubmission(accessWorkflowIDsList, tmpFormRecord)) {           
           *   List accessListOfForm=getAccessWorkflowOfForm(accessWorkflowIDsList,workflowList);
           *   tmpFormRecord.setWorkflowList(accessListOfForm);
           *   tmpFormRecord.setWorkflowListSize(accessListOfForm.size() );
           *   ++rowLoopCnt;
           *   result.add(tmpFormRecord);
           *   
           *   }else{
           *   totalCount--;//current the totalCount of list
           *   }
           *   }
           */
          tmpFormRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }

  private List getAccessWorkflowOfForm(List accessWorkflowList, List workflowList) {
    List result=new ArrayList();
     for (Iterator iter = workflowList.iterator(); iter.hasNext();) {
      WorkflowRecord workflow = (WorkflowRecord) iter.next();
      for (Iterator accessInerator = accessWorkflowList.iterator(); accessInerator.hasNext();) {
        Integer id = (Integer) accessInerator.next();
        if (workflow.getID().intValue()==id.intValue()){
          result.add(workflow );
        } 
      }
    }
     
    return result;
  }

  private boolean isValidFormSubmission(List accessList, FormRecord formSubmission) throws ApplicationException {

    if (Utility.isEmpty(formSubmission.getWorkflowList())) {
      return true;
    }

    List workflowList = formSubmission.getWorkflowList();

    for (Iterator iter = workflowList.iterator(); iter.hasNext();) {
      WorkflowRecord workflow = (WorkflowRecord) iter.next();

      for (Iterator iterator = accessList.iterator(); iterator.hasNext();) {
        Integer workflowID = (Integer) iterator.next();
        if (workflow.getID().intValue() == workflowID.intValue()) {
          return true;
        }
      }
    }

    return false;

  }
  
  /**
   * Release form record by the from record ID.
   * @param formRecordID
   * @param updateCount
   * @throws ApplicationException
   */
  public void releaseFormRecord(String formRecordID,
                                String updateCount)
      throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    //FormRecord tmpFormRecord = (FormRecord)((FormRecord)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("UPDATE FORM_RECORD ");
        sqlStat.append("SET STATUS = ? ");
        sqlStat.append("WHERE  ID = ? AND UPDATE_COUNT = ?");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        setPrepareStatement(preStat, 1, FormRecord.FORM_STATUS_RELEASED);
        setPrepareStatement(preStat, 2, formRecordID);
        setPrepareStatement(preStat, 3, updateCount);
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Get last update from record by formGroupID
   * @param formGroupID
   * @return
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getLastUpdateFormRecordByFormGroupID(
      Integer formGroupID )
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();


    synchronized(dbConn) {
      try {

//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");        
        sqlStat.append("FROM   FORM_RECORD A ");
//        sqlStat.append("WHERE  A.STATUS = ? AND A.RECORD_STATUS = ? ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        sqlStat.append("AND A.FORM_GROUP_ID = ? ");
        sqlStat.append("ORDER BY A.UPDATE_DATE DESC ");

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_RELEASED);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, formGroupID);
        rs = preStat.executeQuery();
        FormRecord tmpFormRecord = new FormRecord();
        if (rs.next()) {

          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs,"FORM_GROUP_ID") );
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
//          tmpFormRecord.setFormCategory(getResultSetString(rs, "FORM_CATEGORY_NAME"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          //return(tmpFormRecord);
        } else {

          //throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
        return(tmpFormRecord);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized Integer getNoOfActiveRecordByFormGroupID(
      Integer formGroupID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT COUNT(*) as NO_OF_ACTIVE_RECORDS ");
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE  A.FORM_GROUP_ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, formGroupID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          return(getResultSetInteger(rs, "NO_OF_ACTIVE_RECORDS"));
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * Get the Fomr Record List by Form Group ID
   * @param formGroupID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getFormRecordListByFormGroupID(
      AbstractSearchForm searchForm)
      throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    ListFormRecordForm formRecordForm = (ListFormRecordForm)searchForm;
    
    synchronized(dbConn) {
      try {
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS,  A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.GROUP_NAME, A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
      	sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS,  A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.GROUP_NAME ");
        sqlStat.append("FROM   FORM_RECORD A, FORM_GROUP B ");
        sqlStat.append("WHERE  A.FORM_GROUP_ID = B.ID AND A.RECORD_STATUS = ? AND B.ID = ?  ");

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }
        if (!Utility.isEmpty(formRecordForm.getSearchFormType())){
          sqlStat.append("AND A.FORM_TYPE = '" + formRecordForm.getSearchFormType() + "' ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, 2, ((ListFormRecordForm)searchForm).getFormGroupID());
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 3, searchKeyword);
        }

        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }

        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        //preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, ((ListFormRecordForm)searchForm).getFormGroupID());
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 3, searchKeyword);
        }

        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, pageSize);
        while (rs.next() && rowLoopCnt<pageSize) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setGroupName(getResultSetString(rs, "GROUP_NAME"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
//          tmpFormRecord.setFormCategory(getResultSetString(rs, "FORM_CATEGORY_NAME"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          tmpFormRecord.setRecordCount(totalNumOfRecord);
          tmpFormRecord.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }


  /**
   * Get workflow relation form list
   * @param workflowID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getWorkflowFormRecordByWorkflowID(
      Integer workflowID )
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
      	sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");      	
        sqlStat.append("FROM   FORM_RECORD A, MTM_WF_RECORD_OBJECT_LINK B ");
        sqlStat.append("WHERE  A.ID = B.OBJECT_ID AND B.WORKFLOW_CATEGORY_ID = 10  AND  A.STATUS = ? AND A.RECORD_STATUS = ? ");
        sqlStat.append("AND B.WORKFLOW_RECORD_ID = ? ");
        sqlStat.append("ORDER BY A.UPDATE_DATE DESC ");

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_RELEASED);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, workflowID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs,"FORM_GROUP_ID") );
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));          
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          result.add(tmpFormRecord) ;
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }




  /**
   * Get workflow relation form list
   * @param workflowID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getFormRecordByStatus(
      String status )
      throws ApplicationException {
    return this.getFormRecordByStatusExpired(status, null);
  }

  /**
   * get form record by status & expired
   * @param status
   * @param expired
   * @return List
   * @throws ApplicationException
   */
  public synchronized List getFormRecordByStatusExpired( String status, String expired) throws ApplicationException {
    return getFormRecordByStatusExpiredForWorkflow(status,expired,null);
  }
  public synchronized List getFormRecordByStatusExpiredForWorkflow( String status, String expired,String orderBy) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT  ");
        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE    A.STATUS = ? AND A.RECORD_STATUS = ? ");
        if (GlobalConstant.TRUE.equals(expired)) {
          //sqlStat.append(" AND (A.END_DATE >= ? OR A.END_DATE IS NULL) ");
          //bill add for EIP-1299 start	
          //sqlStat.append(" AND ( A.START_DATE <=? AND A.END_DATE >= ? OR A.END_DATE IS NULL) ");
          //bill add for EIP-1299 end
          
          //ryan kwan fix for EIP-1299 start
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            sqlStat.append(" AND ( A.START_DATE <= to_date(? , 'YYYY-MM-DD HH24:MI:SS')");
            sqlStat.append(" AND ( A.END_DATE IS NULL OR A.END_DATE  >= to_date(? , 'YYYY-MM-DD HH24:MI:SS')))");
          } else {
            sqlStat.append(" AND ( A.START_DATE <= ? ");
            sqlStat.append(" AND ( A.END_DATE IS NULL OR A.END_DATE  >= ? ))");
          }          
          //ryan kwan fix for EIP-1299 end
        }
        if(Utility.isEmpty(orderBy)){
          sqlStat.append("ORDER BY A.UPDATE_DATE DESC");
        }else{
          sqlStat.append("ORDER BY "+orderBy);
        }
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, status);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (GlobalConstant.TRUE.equals(expired)) {
          Timestamp currentTime = Utility.getCurrentTimestamp();
          this.setPrepareStatement(preStat, 3, TextUtility.formatTimestampToDBDate(currentTime));
          //bill add for EIP-1299 start
          this.setPrepareStatement(preStat, 4, TextUtility.formatTimestampToDBDate(currentTime));
          //bill add for EIP-1299 end
        }
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs,"FORM_GROUP_ID") );
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));                    
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          result.add(tmpFormRecord) ;
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  public synchronized AbstractBaseObject getByFormRecordID(String formRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    WorkflowRecordDAObject wfRecordDAO = new WorkflowRecordDAObject(this.sessionContainer, this.dbConn);
    synchronized(dbConn) {
      try {
//        sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.GROUP_NAME,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
      	sqlStat.append("SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.GROUP_NAME ");
        sqlStat.append("FROM   FORM_RECORD A, FORM_GROUP B ");
        sqlStat.append("WHERE  A.FORM_GROUP_ID = B.ID AND A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, formRecordID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
		  tmpFormRecord.setGroupName(getResultSetString(rs, "GROUP_NAME"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));  
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          
          //get the bind workflow list
          List workflowList = wfRecordDAO.getBindListByFormRecordIDFormGroupID(tmpFormRecord.getID(), tmpFormRecord.getFormGroupID());
          if(!Utility.isEmpty(workflowList)){
            tmpFormRecord.setWorkflowList(workflowList);
            tmpFormRecord.setWorkflowListSize(workflowList.size());
          }
          
          return(tmpFormRecord);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized Integer getNoOfActiveRecordForMonitoringByFormGroupID(
      Integer formGroupID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    PreparedStatement preStat1 = null;
    ResultSet rs1 = null;
    StringBuffer sqlStat1 = new StringBuffer();
    String formRecordID ="";
    synchronized(dbConn) {
      try {
        sqlStat1.append("SELECT DISTINCT FORM_RECORD_ID ");
        sqlStat1.append("FROM FORM_RECORD A, FORM_SUBMISSION C ");
        sqlStat1.append("WHERE  A.ID = C.FORM_RECORD_ID ");
        sqlStat1.append("AND A.STATUS = '" + GlobalConstant.RECORD_RELEASED + "' ");
        sqlStat1.append("AND A.RECORD_STATUS = '" + GlobalConstant.RECORD_STATUS_ACTIVE + "' ");
        sqlStat1.append("AND A.RECORD_STATUS = C.RECORD_STATUS AND C.STATUS <> '"+FormSubmission.FORM_SUBMISSION_STATU_SAVED+"' ");
        preStat1 = dbConn.prepareStatement(sqlStat1.toString());

        rs1= preStat1.executeQuery();
        while (rs1.next()) {
            formRecordID += ((formRecordID.length()>0)?",":"") + getResultSetInteger(rs1, "FORM_RECORD_ID");
        }

        sqlStat.append("SELECT COUNT(*) as NO_OF_ACTIVE_RECORDS ");
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE  A.FORM_GROUP_ID = ? AND A.RECORD_STATUS = ? AND A.STATUS = ? ");
        if (!Utility.isEmpty(formRecordID)) {
          sqlStat.append("AND A.ID IN ("+formRecordID+") ");
        }else{
          sqlStat.append("AND A.ID IN (-1) ");
        }

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, formGroupID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_RELEASED);


        rs = preStat.executeQuery();
        if (rs.next()) {
          return(getResultSetInteger(rs, "NO_OF_ACTIVE_RECORDS"));
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized Integer getNoOfActiveRecordForReportByFormGroupID(
      Integer formGroupID)
      throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    PreparedStatement preStat1 = null;
    ResultSet rs1 = null;
    StringBuffer sqlStat1 = new StringBuffer();
    String formRecordID ="";
    synchronized(dbConn) {
      try {
        sqlStat1.append("SELECT DISTINCT FORM_RECORD_ID ");
        sqlStat1.append("FROM FORM_RECORD A, FORM_SUBMISSION C ");
        sqlStat1.append("WHERE  A.ID = C.FORM_RECORD_ID AND A.STATUS = '"+GlobalConstant.RECORD_RELEASED+"' ");
        sqlStat1.append("AND A.RECORD_STATUS = '"+GlobalConstant.RECORD_STATUS_ACTIVE+"' ");
        sqlStat1.append("AND A.RECORD_STATUS = C.RECORD_STATUS AND C.STATUS <> '"+FormSubmission.FORM_SUBMISSION_STATU_SAVED+"' ");
        preStat1 = dbConn.prepareStatement(sqlStat1.toString());
        rs1= preStat1.executeQuery();
        while (rs1.next()) {
          formRecordID += ((formRecordID.length()>0)?",":"") + getResultSetInteger(rs1, "FORM_RECORD_ID");
        }

        sqlStat.append("SELECT COUNT(*) as NO_OF_ACTIVE_RECORDS ");
        sqlStat.append("FROM   FORM_RECORD A ");
        sqlStat.append("WHERE  A.FORM_GROUP_ID = ? AND A.RECORD_STATUS = ? AND A.STATUS = ? ");
        if (!Utility.isEmpty(formRecordID)) {
          sqlStat.append("AND A.ID IN ("+formRecordID+") ");
        }else{
          sqlStat.append("AND A.ID IN (-1) ");
        }
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, formGroupID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_RELEASED);

        rs = preStat.executeQuery();
        if (rs.next()) {
          return(getResultSetInteger(rs, "NO_OF_ACTIVE_RECORDS"));
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   *
   * @param formRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getSearchElementListByFormRecordID(Integer formRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FORM_RECORD_ID, A.PARENT_PAGE_ID, A.TITLE, A.TITLE_ALT, A.PAGE_NO, A.PAGE_CODE, A.DESCRIPTION, A.DESCRIPTION_ALT, A.TITLE_FONT_COLOR, A.TITLE_FONT_SIZE, A.TITLE_FONT_TYPE, A.TITLE_FONT_STYLE, A.DESC_FONT_COLOR, A.DESC_FONT_SIZE, A.DESC_FONT_TYPE, A.DESC_FONT_STYLE, A.GROUP_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ,A.TITLE_POSITION ");
        sqlStat.append("FROM   FORM_PAGE A ");
        sqlStat.append("WHERE  A.FORM_RECORD_ID = ? AND A.RECORD_STATUS = ? ");
        sqlStat.append("ORDER BY A.PAGE_NO DESC");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, formRecordID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();

        FormSectionDAObject formSectionDAO = new FormSectionDAObject(sessionContainer, dbConn);
        FormElementDAObject formElementDAO = new FormElementDAObject(sessionContainer, dbConn);

        while (rs.next()) {
          List formSectionList = formSectionDAO.getDetailListByFormPageID(getResultSetInteger(rs, "ID"));
          for (int i=0; i<formSectionList.size(); i++){
            FormSection tmpFormSection = new FormSection();
            tmpFormSection = (FormSection)formSectionList.get(i);
            List formElementList = formElementDAO.getSearchListByFormSectionID(tmpFormSection.getID());
            result.addAll(formElementList);
          }
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  public synchronized List getSearchElementListByFormRecordIDElementType(Integer formRecordID,String elementType) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FORM_RECORD_ID, A.PARENT_PAGE_ID, A.TITLE, A.TITLE_ALT, A.PAGE_NO, A.PAGE_CODE, A.DESCRIPTION, A.DESCRIPTION_ALT, A.TITLE_FONT_COLOR, A.TITLE_FONT_SIZE, A.TITLE_FONT_TYPE, A.TITLE_FONT_STYLE, A.DESC_FONT_COLOR, A.DESC_FONT_SIZE, A.DESC_FONT_TYPE, A.DESC_FONT_STYLE, A.GROUP_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ,A.TITLE_POSITION ");
        sqlStat.append("FROM   FORM_PAGE A ");
        sqlStat.append("WHERE  A.FORM_RECORD_ID = ? AND A.RECORD_STATUS = ? ");
        sqlStat.append("ORDER BY A.PAGE_NO DESC");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, formRecordID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();

        FormSectionDAObject formSectionDAO = new FormSectionDAObject(sessionContainer, dbConn);
        FormElementDAObject formElementDAO = new FormElementDAObject(sessionContainer, dbConn);
        List formElementList = new ArrayList();
        while (rs.next()) {
          List formSectionList = formSectionDAO.getDetailListByFormPageID(getResultSetInteger(rs, "ID"));
          for (int i=0; i<formSectionList.size(); i++){
            FormSection tmpFormSection = new FormSection();
            tmpFormSection = (FormSection)formSectionList.get(i);
            if (FormElementType.ELEMENT_DATE_PICKER.equals(elementType)){
              formElementList = formElementDAO.getDatePickerSearchListByFormSectionID(tmpFormSection.getID());   
            }else if (FormElementType.isSelection(elementType)){
              formElementList = formElementDAO.getSelectBoxSearchListByFormSectionID(tmpFormSection.getID());  
            }else if (FormElementType.ELEMENT_TEXT.equals(elementType)){
              formElementList = formElementDAO.getTextBoxSearchListByFormSectionID(tmpFormSection.getID());      
            }
            result.addAll(formElementList);
          }
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get the bind form list by workflow record ID.
   * @param workflowRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getBindFormListByWorkflowRecordID(Integer workflowRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    MtmWfStepFormRecordDAObject stepFormDAO = new MtmWfStepFormRecordDAObject(this.sessionContainer, this.dbConn);
    synchronized(dbConn) {
      try {
//        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");        
        sqlStat.append(" FROM   FORM_RECORD A, MTM_WF_RECORD_OBJECT_LINK B ");
        sqlStat.append(" WHERE  A.ID = B.OBJECT_ID ");
        sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append(" AND A.RECORD_STATUS = ? ");
        sqlStat.append(" AND B.OBJECT_TYPE = ? ");
        sqlStat.append(" AND B.WORKFLOW_RECORD_ID = ? ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.OBJECT_TYPE_FORM);
        this.setPrepareStatement(preStat, 3, workflowRecordID);
        
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));         
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          
          //check whether the form is mandatory submited in the first step of the workflow. 
          if(stepFormDAO.getByFormRecordIDWorkflowRecordID(tmpFormRecord.getID(), workflowRecordID)){
            tmpFormRecord.setMandatory(GlobalConstant.TRUE);
          } else {
            tmpFormRecord.setMandatory(GlobalConstant.FALSE); 
          }
          
          //check if the form bind with the workflow at the first step
          List bindList = stepFormDAO.getListByWorkflowRecordIDFormRecordID(workflowRecordID, tmpFormRecord.getID());
          tmpFormRecord.setHasBindFirstStep(Utility.isEmpty(bindList) ? GlobalConstant.FALSE : GlobalConstant.TRUE);
          
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  /**
   * get the form must be submitted in this step.
   * @param workflowStepID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getSubmitFormListByStepID(Integer workflowStepID) throws ApplicationException {
    return this.getHandleFormListByStepIDPermission(workflowStepID, WorkflowStep.FORM_PROCESS_PERMISSION_SUBMIT);
  }
  
  /**
   * get the form must be operated in this step.
   * @param workflowStepID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getOperateFormListByStepID(Integer workflowStepID) throws ApplicationException {
    return this.getHandleFormListByStepIDPermission(workflowStepID, WorkflowStep.FORM_PROCESS_PERMISSION_CHANGE);
  }
  
  /**
   * get these forms must be handled in this step .
   * @param workflowStepID
   * @param permission
   * @return
   * @throws ApplicationException
   */    
  public synchronized List getHandleFormListByStepIDPermission(Integer workflowStepID, String permission) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
//        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append(" FROM FORM_RECORD A, MTM_WF_STEP_FORM_RECORD B ");
        sqlStat.append(" WHERE A. ID = B.FORM_RECORD_ID ");
        sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append(" AND A.RECORD_STATUS = ? ");
        sqlStat.append(" AND B.PERMISSION = ? ");
        sqlStat.append(" AND B.WORKFLOW_STEP_ID = ?");
        
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, permission);
        this.setPrepareStatement(preStat, 3, workflowStepID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));                   
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get handle form list by workflow progress ID and user ID.
   * @param workflowProgressID
   * @param userID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getHandleFormListByWorkflowProgressIDUserID(Integer workflowProgressID, Integer userID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
//        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.FORM_SUBMISSION_ID, B.PROCESSED,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.FORM_SUBMISSION_ID, B.PROCESSED ");
        sqlStat.append(" FROM FORM_RECORD A, MTM_WF_PROGRESS_ACCESS_FORM B ");
        sqlStat.append(" WHERE A. ID = B.FORM_RECORD_ID ");
        sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append(" AND A.RECORD_STATUS = ? ");        
        sqlStat.append(" AND B.WORKFLOW_PROGRESS_ID = ?");
        sqlStat.append(" AND B.UPDATER_ID = ? ");        
        sqlStat.append(" ORDER BY B.PROCESSED ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowProgressID);
        this.setPrepareStatement(preStat, 3, userID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpFormRecord.setFormSubmissionID(getResultSetInteger(rs, "FORM_SUBMISSION_ID"));
          tmpFormRecord.setFormProcessed(getResultSetString(rs, "PROCESSED"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));                   
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get the mandatory submit form list in the step.
   * @param workflowStepID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getMandatorySubmitFormListByStepID(Integer workflowStepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
//        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
      	sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append(" FROM FORM_RECORD A, MTM_WF_STEP_FORM_RECORD B ");
        sqlStat.append(" WHERE A. ID = B.FORM_RECORD_ID ");
        sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append(" AND A.RECORD_STATUS = ? ");
        sqlStat.append(" AND B.PERMISSION = ? ");
        sqlStat.append(" AND B.WORKFLOW_STEP_ID = ? ");
        sqlStat.append(" AND B.MANDATORY = ? ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, WorkflowStep.FORM_PROCESS_PERMISSION_SUBMIT);
        this.setPrepareStatement(preStat, 3, workflowStepID);
        this.setPrepareStatement(preStat, 4, GlobalConstant.TRUE);
        
        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));                   
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get pending submit form list by track ID.
   * @param trackID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getPendingSubmitFormListByTrackID(Integer trackID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
//        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.ID AS FORM_SUBMISSION_ID,A.PREFIX, A.SUFFIX ,A.START_NO ,A.LENGTH ,A.CURRENT_NO ,A.INCREMENT ");
        sqlStat.append(" SELECT A.ID, A.FORM_CODE, A.FORM_VERSION, A.FORM_TYPE, A.FORM_DOC_ID, A.FORM_GROUP_ID, A.TITLE, A.TITLE_ALT, A.DESCRIPTION, A.DESCRIPTION_ALT, A.STATUS, A.START_DATE, A.END_DATE, A.DISPLAY_ELEMENT_ID, A.FORM_CATEGORY_ID, A.TRACK_CLASS, A.SUBMISSION_CLASS, A.AUTO_NUMBERING, A.LAST_PAGE_CODE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.ID AS FORM_SUBMISSION_ID ");        
        sqlStat.append(" FROM FORM_RECORD A, FORM_SUBMISSION B ");
        sqlStat.append(" LEFT JOIN MTM_WF_PROGRESS_SYSTEM_OBJECT C ON (B.ID = C.OBJECT_ID AND C.BEAN_CLASS_NAME = 'com.dcivision.form.bean.FormSubmission') ");
        sqlStat.append(" WHERE A. ID = B.FORM_RECORD_ID ");
        sqlStat.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
        sqlStat.append(" AND A.RECORD_STATUS = ? ");
        sqlStat.append(" AND B.TRACK_ID = ? ");
        sqlStat.append(" AND B.STATUS = ? ");
        sqlStat.append(" AND C.ID IS NULL ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, trackID);
        this.setPrepareStatement(preStat, 3, FormSubmission.FORM_SUBMISSION_STATU_PENDING);

        rs = preStat.executeQuery();
        while (rs.next()) {
          FormRecord tmpFormRecord = new FormRecord();
          tmpFormRecord.setID(getResultSetInteger(rs, "ID"));
          tmpFormRecord.setFormCode(getResultSetString(rs, "FORM_CODE"));
          tmpFormRecord.setFormVersion(getResultSetString(rs, "FORM_VERSION"));
          tmpFormRecord.setFormType(getResultSetInteger(rs, "FORM_TYPE"));
          tmpFormRecord.setFormDocID(getResultSetInteger(rs, "FORM_DOC_ID"));
          tmpFormRecord.setFormGroupID(getResultSetInteger(rs, "FORM_GROUP_ID"));
          tmpFormRecord.setTitle(getResultSetString(rs, "TITLE"));
          tmpFormRecord.setTitleAlt(getResultSetString(rs, "TITLE_ALT"));
          tmpFormRecord.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpFormRecord.setDescriptionAlt(getResultSetString(rs, "DESCRIPTION_ALT"));
          tmpFormRecord.setStatus(getResultSetString(rs, "STATUS"));
          tmpFormRecord.setStartDate(getResultSetTimestamp(rs, "START_DATE"));
          tmpFormRecord.setEndDate(getResultSetTimestamp(rs, "END_DATE"));
          tmpFormRecord.setDisplayElementID(getResultSetInteger(rs, "DISPLAY_ELEMENT_ID"));
          tmpFormRecord.setFormCategoryID(getResultSetInteger(rs, "FORM_CATEGORY_ID"));
          tmpFormRecord.setTrackClass(getResultSetString(rs, "TRACK_CLASS"));
          tmpFormRecord.setSubmissionClass(getResultSetString(rs, "SUBMISSION_CLASS"));
          tmpFormRecord.setAutoNumbering(getResultSetString(rs, "AUTO_NUMBERING"));
          tmpFormRecord.setLastPageCode(getResultSetString(rs, "LAST_PAGE_CODE"));
          tmpFormRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpFormRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpFormRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpFormRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpFormRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpFormRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
//          tmpFormRecord.setPrefix(getResultSetString(rs, "PREFIX"));
//          tmpFormRecord.setSuffix(getResultSetString(rs, "SUFFIX"));
//          tmpFormRecord.setStart_no(getResultSetInteger(rs, "START_NO"));
//          tmpFormRecord.setLength(getResultSetInteger(rs, "LENGTH"));
//          tmpFormRecord.setCurrent_no(getResultSetInteger(rs, "CURRENT_NO"));
//          tmpFormRecord.setIncrement(getResultSetInteger(rs, "INCREMENT"));                   
          tmpFormRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpFormRecord.getCreatorID()));
          tmpFormRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpFormRecord.getUpdaterID()));
          
          tmpFormRecord.setFormSubmissionID(getResultSetInteger(rs, "FORM_SUBMISSION_ID"));
          result.add(tmpFormRecord);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get mandatory field by recordID
   * @param formRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getRelatedItemListByFormRecordID(Integer formRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.FORM_RECORD_ID, A.PARENT_PAGE_ID, A.TITLE, A.TITLE_ALT, A.PAGE_NO, A.PAGE_CODE, A.DESCRIPTION, A.DESCRIPTION_ALT, A.TITLE_FONT_COLOR, A.TITLE_FONT_SIZE, A.TITLE_FONT_TYPE, A.TITLE_FONT_STYLE, A.DESC_FONT_COLOR, A.DESC_FONT_SIZE, A.DESC_FONT_TYPE, A.DESC_FONT_STYLE, A.GROUP_ID, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ,A.TITLE_POSITION ");
        sqlStat.append("FROM   FORM_PAGE A ");
        sqlStat.append("WHERE  A.FORM_RECORD_ID = ? AND A.RECORD_STATUS = ? ");
        sqlStat.append("ORDER BY A.PAGE_NO DESC");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, formRecordID);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();

        FormSectionDAObject formSectionDAO = new FormSectionDAObject(sessionContainer, dbConn);
        FormElementDAObject formElementDAO = new FormElementDAObject(sessionContainer, dbConn);

        while (rs.next()) {
          List formSectionList = formSectionDAO.getDetailListByFormPageID(getResultSetInteger(rs, "ID"));
          for (int i=0; i<formSectionList.size(); i++){
            FormSection tmpFormSection = new FormSection();
            tmpFormSection = (FormSection)formSectionList.get(i);
            List formElementList = formElementDAO.getRelatedItemElementListByID(tmpFormSection.getID());
            result.addAll(formElementList);
          }
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
}